﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Adams.Admin.DataAccess.Events
{
    public static class CreateEvent
    {
        public static Adams.Admin.DataTransfer.Events.CreateEvent.PageLoad PageLoad(int occuranceId)
        {
            var data = new Adams.Admin.DataTransfer.Events.CreateEvent.PageLoad();
            data.Event = new Shared.Entities.Events.Event();
            data.Event.Calendar = new Shared.Entities.Events.Calendar();
            data.Occurance = new Shared.Entities.Events.Occurance();
            data.Calendars = new Shared.Entities.Events.CalendarCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@OccuranceID > 0)
BEGIN
    SELECT O.OccuranceID,  O.Title, O.ShortDescription, O.Description, O.Location, O.StartDate, O.EndDate, O.TimeZone, O.IsAllDay,
    O.EventID, E.CalendarID, E.IsRepeat, E.RepeatType, E.RepeatEndType, E.RepeatEndOccurences, E.RepeatEndDate, E.RepeatEvery, E.RepeatStartsOn, 
    E.RepeatWeeklySun, E.RepeatWeeklyMon, E.RepeatWeeklyTeu, E.RepeatWeeklyWed, E.RepeatWeeklyThu, E.RepeatWeeklyFri, 
    E.RepeatWeeklySat, E.RepeatMonthlyBy
    FROM Events.Occurances O 
    INNER JOIN Events.Events E ON O.EventID = E.EventID
    WHERE OccuranceID = @OccuranceID
END

SELECT * FROM Events.Calendars ORDER BY Name

", CommandType.Text,
            DB.CreateParameter("@OccuranceID", occuranceId, SqlDbType.Int)))
            {
                if (occuranceId > 0)
                {
                    dr.Read();
                    data.Occurance.ID = dr["OccuranceID"].DbInt();
                    data.Occurance.Title = dr["Title"].DbString();
                    data.Occurance.ShortDescription = dr["ShortDescription"].DbString();
                    data.Occurance.Description = dr["Description"].DbString();
                    data.Occurance.Location = dr["Location"].DbString();
                    data.Occurance.StartDate = dr["StartDate"].DbDate();
                    data.Occurance.EndDate = dr["EndDate"].DbDate();
                    data.Occurance.TimeZone = dr["TimeZone"].DbInt();
                    data.Occurance.IsAllDay = dr["IsAllDay"].DbBool();

                    data.Event.ID = dr["EventID"].DbInt();
                    data.Event.IsRepeat = dr["IsRepeat"].DbBool();
                    data.Event.RepeatType = dr["RepeatType"].DbInt();
                    data.Event.RepeatEndType = dr["RepeatEndType"].DbInt();
                    data.Event.RepeatEndOccurences = dr["RepeatEndOccurences"].DbInt();
                    data.Event.RepeatEndDate = dr["RepeatEndDate"].DbDate();
                    data.Event.RepeatEvery = dr["RepeatEvery"].DbInt();
                    data.Event.RepeatStartsOn = dr["RepeatStartsOn"].DbDate();
                    data.Event.RepeatWeeklySun = dr["RepeatWeeklySun"].DbBool();
                    data.Event.RepeatWeeklyMon = dr["RepeatWeeklyMon"].DbBool();
                    data.Event.RepeatWeeklyTeu = dr["RepeatWeeklyTeu"].DbBool();
                    data.Event.RepeatWeeklyWed = dr["RepeatWeeklyWed"].DbBool();
                    data.Event.RepeatWeeklyThu = dr["RepeatWeeklyThu"].DbBool();
                    data.Event.RepeatWeeklyFri = dr["RepeatWeeklyFri"].DbBool();
                    data.Event.RepeatWeeklySat = dr["RepeatWeeklySat"].DbBool();
                    data.Event.RepeatMonthlyBy = dr["RepeatMonthlyBy"].DbInt();

                    data.Event.Calendar.ID = dr["CalendarID"].DbInt();

                    dr.NextResult();
                }

                while (dr.Read())
                {
                    var calendar = new Shared.Entities.Events.Calendar();
                    calendar.ID = dr["CalendarID"].DbInt();
                    calendar.Name = dr["Name"].DbString();
                    calendar.Description = dr["Description"].DbString();
                    calendar.Color = dr["Color"].DbString();
                    data.Calendars.Add(calendar);
                }
            }

            return data;
        }

        public static void Save(DataTransfer.Events.CreateEvent.SaveEvent data, int EditRepeatType)
        {
            if (data.Event.ID == 0)
            {
                #region New Event
                int eventId = DB.RunScalar(@"
    INSERT INTO Events.Events (
        CalendarID
        ,Title
        ,ShortDescription
        ,Description
        ,Location
        ,StartDate
        ,EndDate
        ,TimeZone
        ,IsAllDay
        ,IsRepeat
        ,RepeatType
        ,RepeatEndType
        ,RepeatEndOccurences
        ,RepeatEndDate
        ,RepeatEvery
        ,RepeatStartsOn
        ,RepeatWeeklySun
        ,RepeatWeeklyMon
        ,RepeatWeeklyTeu
        ,RepeatWeeklyWed
        ,RepeatWeeklyThu
        ,RepeatWeeklyFri
        ,RepeatWeeklySat
        ,RepeatMonthlyBy)
    VALUES (
        @CalendarID
        ,@Title
        ,@ShortDescription
        ,@Description
        ,@Location
        ,@StartDate
        ,@EndDate
        ,@TimeZone
        ,@IsAllDay
        ,@IsRepeat
        ,@RepeatType
        ,@RepeatEndType
        ,@RepeatEndOccurences
        ,@RepeatEndDate
        ,@RepeatEvery
        ,@RepeatStartsOn
        ,@RepeatWeeklySun
        ,@RepeatWeeklyMon
        ,@RepeatWeeklyTeu
        ,@RepeatWeeklyWed
        ,@RepeatWeeklyThu
        ,@RepeatWeeklyFri
        ,@RepeatWeeklySat
        ,@RepeatMonthlyBy)
    SELECT SCOPE_IDENTITY() AS EventID
    ", CommandType.Text,
        DB.CreateParameter("@EventID", data.Event.ID.ToInt()),
        DB.CreateParameter("@CalendarID", data.Event.Calendar.ID),
        DB.CreateParameter("@Title", data.Event.Title.ToNString()),
        DB.CreateParameter("@ShortDescription", data.Event.ShortDescription.ToNString()),
        DB.CreateParameter("@Description", data.Event.Description.ToNString()),
        DB.CreateParameter("@Location", data.Event.Location.ToNString()),
        DB.CreateParameter("@StartDate", data.Event.StartDate),
        DB.CreateParameter("@EndDate", data.Event.EndDate),
        DB.CreateParameter("@TimeZone", data.Event.TimeZone),
        DB.CreateParameter("@IsAllDay", data.Event.IsAllDay),
        DB.CreateParameter("@IsRepeat", data.Event.IsRepeat),
        DB.CreateParameter("@RepeatType", data.Event.RepeatType),
        DB.CreateParameter("@RepeatEndType", data.Event.RepeatEndType),
        DB.CreateParameter("@RepeatEndOccurences", data.Event.RepeatEndOccurences),
        DB.CreateParameter("@RepeatEndDate", data.Event.RepeatEndDate),
        DB.CreateParameter("@RepeatEvery", data.Event.RepeatEvery),
        DB.CreateParameter("@RepeatStartsOn", data.Event.RepeatStartsOn),
        DB.CreateParameter("@RepeatWeeklySun", data.Event.RepeatWeeklySun),
        DB.CreateParameter("@RepeatWeeklyMon", data.Event.RepeatWeeklyMon),
        DB.CreateParameter("@RepeatWeeklyTeu", data.Event.RepeatWeeklyTeu),
        DB.CreateParameter("@RepeatWeeklyWed", data.Event.RepeatWeeklyWed),
        DB.CreateParameter("@RepeatWeeklyThu", data.Event.RepeatWeeklyThu),
        DB.CreateParameter("@RepeatWeeklyFri", data.Event.RepeatWeeklyFri),
        DB.CreateParameter("@RepeatWeeklySat", data.Event.RepeatWeeklySat),
        DB.CreateParameter("@RepeatMonthlyBy", data.Event.RepeatMonthlyBy)).DbInt();

                StringBuilder sb = new StringBuilder();
                sb.Append(@"
DELETE Events.Occurances WHERE EventID = (SELECT EventID FROM Events.Occurances WHERE OccuranceID = " + data.Event.ID.ToString() + @")
");
                foreach (var e in data.Event.Occurances)
                {
                    sb.Append(@"
INSERT INTO Events.Occurances (
        EventID
        ,Title
        ,ShortDescription
        ,Description
        ,Location
        ,StartDate
        ,EndDate
        ,TimeZone
        ,IsAllDay)
    VALUES (
        " + eventId.ToString() + @"
        ,'" + e.Title.ToSQLString() + @"'
        ,'" + e.ShortDescription.ToSQLString() + @"'
        ,'" + e.Description.ToSQLString() + @"'
        ,'" + e.Location.ToSQLString() + @"'
        ,'" + e.StartDate.ToString() + @"'
        ,'" + e.EndDate.ToString() + @"'
        ," + e.TimeZone.ToString() + @"
        ," + e.IsAllDay.ToSQLString() + @")
");
                }

                DB.Run(sb.ToString(), CommandType.Text);
                #endregion
            }
            else
            {
                if (data.Event.IsRepeat)
                {
                    if (EditRepeatType == 1)
                    {
                        #region Edit only this
                        DB.Run(@"
    UPDATE Events.Events SET
        CalendarID = @CalendarID
    WHERE EventID = (SELECT EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID)

    UPDATE Events.Occurances SET
        Title = @Title
        ,ShortDescription = @ShortDescription
        ,Description = @Description
        ,Location = @Location
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,TimeZone = @TimeZone
        ,IsAllDay = @IsAllDay
    WHERE OccuranceID = @OccuranceID
    ", CommandType.Text,
                        DB.CreateParameter("@OccuranceID", data.Event.ID.ToInt()),
                        DB.CreateParameter("@CalendarID", data.Event.Calendar.ID),
                        DB.CreateParameter("@Title", data.Event.Title),
                        DB.CreateParameter("@ShortDescription", data.Event.ShortDescription),
                        DB.CreateParameter("@Description", data.Event.Description),
                        DB.CreateParameter("@Location", data.Event.Location),
                        DB.CreateParameter("@StartDate", data.Event.StartDate),
                        DB.CreateParameter("@EndDate", data.Event.EndDate),
                        DB.CreateParameter("@TimeZone", data.Event.TimeZone),
                        DB.CreateParameter("@IsAllDay", data.Event.IsAllDay));

                        #endregion
                    }
                    else if (EditRepeatType == 2)
                    {
                        #region Edit following
                        DB.Run(@"
    UPDATE Events.Events SET
        CalendarID = @CalendarID
        ,Title = @Title
        ,ShortDescription = @ShortDescription
        ,Description = @Description
        ,Location = @Location
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,TimeZone = @TimeZone
        ,IsAllDay = @IsAllDay
        ,IsRepeat = @IsRepeat
        ,RepeatType = @RepeatType
        ,RepeatEndType = @RepeatEndType
        ,RepeatEndOccurences = @RepeatEndOccurences
        ,RepeatEndDate = @RepeatEndDate
        ,RepeatEvery = @RepeatEvery
        ,RepeatStartsOn = @RepeatStartsOn
        ,RepeatWeeklySun = @RepeatWeeklySun
        ,RepeatWeeklyMon = @RepeatWeeklyMon
        ,RepeatWeeklyTeu = @RepeatWeeklyTeu
        ,RepeatWeeklyWed = @RepeatWeeklyWed
        ,RepeatWeeklyThu = @RepeatWeeklyThu
        ,RepeatWeeklyFri = @RepeatWeeklyFri
        ,RepeatWeeklySat = @RepeatWeeklySat
        ,RepeatMonthlyBy = @RepeatMonthlyBy
    WHERE EventID = (SELECT EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID)
    ", CommandType.Text,
                        DB.CreateParameter("@OccuranceID", data.Event.ID.ToInt()),
                        DB.CreateParameter("@CalendarID", data.Event.Calendar.ID),
                        DB.CreateParameter("@Title", data.Event.Title),
                        DB.CreateParameter("@ShortDescription", data.Event.ShortDescription),
                        DB.CreateParameter("@Description", data.Event.Description),
                        DB.CreateParameter("@Location", data.Event.Location),
                        DB.CreateParameter("@StartDate", data.Event.StartDate),
                        DB.CreateParameter("@EndDate", data.Event.EndDate),
                        DB.CreateParameter("@TimeZone", data.Event.TimeZone),
                        DB.CreateParameter("@IsAllDay", data.Event.IsAllDay),
                        DB.CreateParameter("@IsRepeat", data.Event.IsRepeat),
                        DB.CreateParameter("@RepeatType", data.Event.RepeatType),
                        DB.CreateParameter("@RepeatEndType", data.Event.RepeatEndType),
                        DB.CreateParameter("@RepeatEndOccurences", data.Event.RepeatEndOccurences),
                        DB.CreateParameter("@RepeatEndDate", data.Event.RepeatEndDate),
                        DB.CreateParameter("@RepeatEvery", data.Event.RepeatEvery),
                        DB.CreateParameter("@RepeatStartsOn", data.Event.RepeatStartsOn),
                        DB.CreateParameter("@RepeatWeeklySun", data.Event.RepeatWeeklySun),
                        DB.CreateParameter("@RepeatWeeklyMon", data.Event.RepeatWeeklyMon),
                        DB.CreateParameter("@RepeatWeeklyTeu", data.Event.RepeatWeeklyTeu),
                        DB.CreateParameter("@RepeatWeeklyWed", data.Event.RepeatWeeklyWed),
                        DB.CreateParameter("@RepeatWeeklyThu", data.Event.RepeatWeeklyThu),
                        DB.CreateParameter("@RepeatWeeklyFri", data.Event.RepeatWeeklyFri),
                        DB.CreateParameter("@RepeatWeeklySat", data.Event.RepeatWeeklySat),
                        DB.CreateParameter("@RepeatMonthlyBy", data.Event.RepeatMonthlyBy));

                        StringBuilder sb = new StringBuilder();
                        sb.Append(@"
DECLARE @EventID int
SELECT @EventID = EventID FROM Events.Occurances WHERE OccuranceID = " + data.Event.ID.ToString() + @"
DELETE Events.Occurances WHERE EventID = @EventID
AND DATEDIFF(d, '" + data.Event.StartDate.ToString() + @"', StartDate)  >= 0
");
                        foreach (var e in data.Event.Occurances)
                        {
                            if (Shared.DateHelper.DiffDays(data.Event.StartDate, e.StartDate) >= 0)
                            {
                                sb.Append(@"
INSERT INTO Events.Occurances (
        EventID
        ,Title
        ,ShortDescription
        ,Description
        ,Location
        ,StartDate
        ,EndDate
        ,TimeZone
        ,IsAllDay)
    VALUES (
        @EventID
        ,'" + e.Title.ToSQLString() + @"'
        ,'" + e.ShortDescription.ToSQLString() + @"'
        ,'" + e.Description.ToSQLString() + @"'
        ,'" + e.Location.ToSQLString() + @"'
        ,'" + e.StartDate.ToString() + @"'
        ,'" + e.EndDate.ToString() + @"'
        ," + e.TimeZone.ToString() + @"
        ," + e.IsAllDay.ToSQLString() + @")
");
                            }
                        }

                        DB.Run(sb.ToString(), CommandType.Text);
                        #endregion
                    }
                    else if (EditRepeatType == 3)
                    {
                        #region Edit all
                        DB.Run(@"
    UPDATE Events.Events SET
        CalendarID = @CalendarID
        ,Title = @Title
        ,ShortDescription = @ShortDescription
        ,Description = @Description
        ,Location = @Location
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,TimeZone = @TimeZone
        ,IsAllDay = @IsAllDay
        ,IsRepeat = @IsRepeat
        ,RepeatType = @RepeatType
        ,RepeatEndType = @RepeatEndType
        ,RepeatEndOccurences = @RepeatEndOccurences
        ,RepeatEndDate = @RepeatEndDate
        ,RepeatEvery = @RepeatEvery
        ,RepeatStartsOn = @RepeatStartsOn
        ,RepeatWeeklySun = @RepeatWeeklySun
        ,RepeatWeeklyMon = @RepeatWeeklyMon
        ,RepeatWeeklyTeu = @RepeatWeeklyTeu
        ,RepeatWeeklyWed = @RepeatWeeklyWed
        ,RepeatWeeklyThu = @RepeatWeeklyThu
        ,RepeatWeeklyFri = @RepeatWeeklyFri
        ,RepeatWeeklySat = @RepeatWeeklySat
        ,RepeatMonthlyBy = @RepeatMonthlyBy
    WHERE EventID = (SELECT EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID)
    ", CommandType.Text,
                        DB.CreateParameter("@OccuranceID", data.Event.ID.ToInt()),
                        DB.CreateParameter("@CalendarID", data.Event.Calendar.ID),
                        DB.CreateParameter("@Title", data.Event.Title),
                        DB.CreateParameter("@ShortDescription", data.Event.ShortDescription),
                        DB.CreateParameter("@Description", data.Event.Description),
                        DB.CreateParameter("@Location", data.Event.Location),
                        DB.CreateParameter("@StartDate", data.Event.StartDate),
                        DB.CreateParameter("@EndDate", data.Event.EndDate),
                        DB.CreateParameter("@TimeZone", data.Event.TimeZone),
                        DB.CreateParameter("@IsAllDay", data.Event.IsAllDay),
                        DB.CreateParameter("@IsRepeat", data.Event.IsRepeat),
                        DB.CreateParameter("@RepeatType", data.Event.RepeatType),
                        DB.CreateParameter("@RepeatEndType", data.Event.RepeatEndType),
                        DB.CreateParameter("@RepeatEndOccurences", data.Event.RepeatEndOccurences),
                        DB.CreateParameter("@RepeatEndDate", data.Event.RepeatEndDate),
                        DB.CreateParameter("@RepeatEvery", data.Event.RepeatEvery),
                        DB.CreateParameter("@RepeatStartsOn", data.Event.RepeatStartsOn),
                        DB.CreateParameter("@RepeatWeeklySun", data.Event.RepeatWeeklySun),
                        DB.CreateParameter("@RepeatWeeklyMon", data.Event.RepeatWeeklyMon),
                        DB.CreateParameter("@RepeatWeeklyTeu", data.Event.RepeatWeeklyTeu),
                        DB.CreateParameter("@RepeatWeeklyWed", data.Event.RepeatWeeklyWed),
                        DB.CreateParameter("@RepeatWeeklyThu", data.Event.RepeatWeeklyThu),
                        DB.CreateParameter("@RepeatWeeklyFri", data.Event.RepeatWeeklyFri),
                        DB.CreateParameter("@RepeatWeeklySat", data.Event.RepeatWeeklySat),
                        DB.CreateParameter("@RepeatMonthlyBy", data.Event.RepeatMonthlyBy));

                        StringBuilder sb = new StringBuilder();
                        sb.Append(@"
DECLARE @EventID int
SELECT @EventID = EventID FROM Events.Occurances WHERE OccuranceID = " + data.Event.ID.ToString() + @"
DELETE Events.Occurances WHERE EventID = @EventID
");
                        foreach (var e in data.Event.Occurances)
                        {
                            sb.Append(@"
INSERT INTO Events.Occurances (
        EventID
        ,Title
        ,ShortDescription
        ,Description
        ,Location
        ,StartDate
        ,EndDate
        ,TimeZone
        ,IsAllDay)
    VALUES (
        @EventID
        ,'" + e.Title.ToSQLString() + @"'
        ,'" + e.ShortDescription.ToSQLString() + @"'
        ,'" + e.Description.ToSQLString() + @"'
        ,'" + e.Location.ToSQLString() + @"'
        ,'" + e.StartDate.ToString() + @"'
        ,'" + e.EndDate.ToString() + @"'
        ," + e.TimeZone.ToString() + @"
        ," + e.IsAllDay.ToSQLString() + @")
");
                        }

                        DB.Run(sb.ToString(), CommandType.Text);
                        #endregion
                    }
                }
                else
                {
                    #region Non recurring
                    DB.Run(@"

    UPDATE Events.Events SET
        CalendarID = @CalendarID,
        Title = @Title
        ,ShortDescription = @ShortDescription
        ,Description = @Description
        ,Location = @Location
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,TimeZone = @TimeZone
        ,IsAllDay = @IsAllDay
    WHERE EventID = (SELECT EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID)

    UPDATE Events.Occurances SET
        Title = @Title
        ,ShortDescription = @ShortDescription
        ,Description = @Description
        ,Location = @Location
        ,StartDate = @StartDate
        ,EndDate = @EndDate
        ,TimeZone = @TimeZone
        ,IsAllDay = @IsAllDay
    WHERE OccuranceID = @OccuranceID
    ", CommandType.Text,
                        DB.CreateParameter("@OccuranceID", data.Event.ID.ToInt()),
                        DB.CreateParameter("@CalendarID", data.Event.Calendar.ID),
                        DB.CreateParameter("@Title", data.Event.Title),
                        DB.CreateParameter("@ShortDescription", data.Event.ShortDescription),
                        DB.CreateParameter("@Description", data.Event.Description),
                        DB.CreateParameter("@Location", data.Event.Location),
                        DB.CreateParameter("@StartDate", data.Event.StartDate),
                        DB.CreateParameter("@EndDate", data.Event.EndDate),
                        DB.CreateParameter("@TimeZone", data.Event.TimeZone),
                        DB.CreateParameter("@IsAllDay", data.Event.IsAllDay));
                }
                    #endregion
            }
        }

        public static void Delete(DataTransfer.Events.CreateEvent.DeleteInput data)
        {
            if (data.IsRepeat)
            {
                if (data.EditRepeatType.ToInt() == 1)
                {
                    #region Edit only this
                    DB.Run(@"
DELETE Events.Occurances WHERE OccuranceID = @OccuranceID
", CommandType.Text,
                    DB.CreateParameter("@OccuranceID", data.OccuranceID.ToInt()));

                    #endregion
                }
                else if (data.EditRepeatType.ToInt() == 2)
                {
                    #region Edit following
                    DB.Run(@"
DECLARE @EventID int
SELECT @EventID = EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID
DELETE Events.Occurances WHERE EventID = @EventID
AND DATEDIFF(d, '" + data.StartDate + @"', StartDate)  >= 0
", CommandType.Text,
                    DB.CreateParameter("@OccuranceID", data.OccuranceID.ToInt()));
                    #endregion
                }
                else if (data.EditRepeatType.ToInt() == 3)
                {
                    #region Edit all
                    DB.Run(@"
DECLARE @EventID int
SELECT @EventID = EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID
DELETE Events.Occurances WHERE EventID = @EventID
DELETE Events.Events WHERE EventID = @EventID
", CommandType.Text,
                    DB.CreateParameter("@OccuranceID", data.OccuranceID.ToInt()));
                    #endregion
                }
            }
            else
            {
                #region Non recurring
                DB.Run(@"
DECLARE @EventID int
SELECT @EventID = EventID FROM Events.Occurances WHERE OccuranceID = @OccuranceID
DELETE Events.Occurances WHERE EventID = @EventID
DELETE Events.Events WHERE EventID = @EventID
", CommandType.Text,
                    DB.CreateParameter("@OccuranceID", data.OccuranceID.ToInt()));
            }
                #endregion
        }
    }
}